# coding: utf8

import pymysql
import sqlite3


class Task62:
    """
    连接数据库和演示SQL语句的使用
    在初始化方法中建立数据库的连接
    在close方法中关闭连接
    在其它方法中使用SQL语句的使用：
        创建数据表:create_tables
        删除数据表:drop_table
        插入数据:insert_tables
        查询数据
        显示sqlite数据库中的表：show_tables
    """

    def __init__(self, db_driver=None):
        self.dbdriver = db_driver
        if db_driver == pymysql:
            self.conn = pymysql.connect(
                host="localhost",
                port=3306,
                user="root",
                password="123456",
                database='testdb'
            )
        if db_driver == sqlite3:
            self.conn = sqlite3.connect("testdb")
        self.cursor = self.conn.cursor()

    def close(self):
        self.cursor.close()
        self.conn.commit()
        self.conn.close()

    def drop_table(self, table_name='stu_info'):
        sql = """DROP TABLE IF EXISTS {}"""
        self.cursor.execute(sql.format(table_name))

    def create_tables(self):
        # create stu_id
        table_name = 'stu_info'
        self.drop_table(table_name)
        sql = """
            CREATE TABLE {} 
            (sno CHAR(4) PRIMARY KEY,
             name VARCHAR(20),
             cno CHAR(2),
             pno CHAR(3))
            """
        cursor = self.conn.cursor()
        cursor.execute(sql.format(table_name))

        # create stu_score
        table_name = 'stu_course'
        self.drop_table(table_name)
        sql = """
            CREATE TABLE {} 
            (cno CHAR(2) PRIMARY KEY,
             course VARCHAR(20),
             score INT)
            """
        cursor = self.conn.cursor()
        cursor.execute(sql.format(table_name))

        # create stu_spec
        table_name = 'stu_spec'
        self.drop_table(table_name)
        sql = """
            CREATE TABLE {} 
            (pno CHAR(3) PRIMARY KEY,
             spec VARCHAR(20))
            """
        cursor = self.conn.cursor()
        cursor.execute(sql.format(table_name))

    def show_sqlite_tables(self):
        cursor = self.conn.cursor()
        cursor.execute("select name from sqlite_master where type = 'table' order by name")
        for r in cursor.fetchall():
            print(r)

    def show_mysql_tables(self):
        cursor = self.conn.cursor()
        # cursor.execute("select name from sqlite_master where type = 'table' order by name")
        # for r in cursor.fetchall():
        #     print(r)
        return cursor.execute("show tables")

    def insert_tables(self):
        import faker
        fk = faker.Faker('zh_CN')
        record1 = 6
        record2 = 3
        record3 = 5
        cnos = ["{:02d}".format(j+11) for j in range(record2)]
        pnos = ["{:03d}".format(j+1) for j in range(record3)]

        # isnert values to stu_id
        table_name = "stu_info"
        sql_insert = "INSERT INTO {} VALUES ".format(table_name)
        for j in range(record1):
            sql_insert += \
                "('{:04d}', '{}', '{}', '{}'),".format(
                    j + 1 + 1000,
                    fk.name(),
                    fk.random.sample(cnos+['15', '17'], 1)[0],      # add some no not in sno
                    fk.random.sample(pnos+['055'], 1)[0]       # add some no not in pno
                )
        sql_insert = sql_insert[:-1]
        cursor = self.conn.cursor()
        # print(sql_insert)
        cursor.execute(sql_insert)
        cursor.close()

        # isnert values to stu_score
        table_name = "stu_course"
        # cnos = [j+11 for j in fk.random.sample(range(record1), record2)]
        courses = ["语文", "数学", "物理"]
        # courses2 = fk.random.sample(courses, counts=(record1, record1, record1), k=record1)
        scores = [j+3 for j in range(record1)]
        sql_insert = "INSERT INTO {} VALUES ".format(table_name)
        for cno in range(record2):
            # print(cno)
            sql_insert += \
                "('{:02d}', '{}', '{}'),".format(
                    cno+11,
                    courses[cno],
                    scores[cno]
                )
        sql_insert = sql_insert[:-1]
        cursor = self.conn.cursor()
        cursor.execute(sql_insert)
        cursor.close()

        # isnert values to stu_spec
        specs = ['体育', '绘画', '唱歌', '书法', '摄影']
        table_name = "stu_spec"
        sql_insert = "INSERT INTO {} VALUES ".format(table_name)
        for j in range(record3):
            sql_insert += \
                "('{:03d}', '{}'),".format(
                    j + 1,
                    specs[j]
                )
        sql_insert = sql_insert[:-1]
        cursor = self.conn.cursor()
        cursor.execute(sql_insert)
        cursor.close()

    def select_from_multi_tables(self):
        sql = """
        select a.sno, a.name, b.score, c.spec from stu_info a, stu_course b, stu_spec c
        WHERE a.cno=b.cno and a.pno=c.pno
        """
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()
        print("-" * 100)
        self.sprint(sql)
        for row in cursor.fetchall():
            print(row)
        cursor.close()

    def select_left_join(self):
        sql = """
        SELECT stu_info.sno, stu_info.name, stu_course.score, stu_spec.spec 
        FROM stu_info
        LEFT JOIN stu_course
        ON stu_info.cno = stu_course.cno
        LEFT JOIN stu_spec
        ON stu_info.pno = stu_spec.pno 
        WHERE stu_course.score > 1
        """
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()
        print("-" * 100)
        self.sprint(sql)
        for row in cursor.fetchall():
            print(row)
        cursor.close()

    def select_right_join(self):
        if self.dbdriver == sqlite3:
            print("RIGHT and FULL OUTER JOINs not supported in sqlite3")
            return
        sql = """
        SELECT stu_info.sno, stu_info.name, stu_course.score, stu_spec.spec 
        FROM stu_info 
        RIGHT JOIN stu_course
        ON stu_info.cno = stu_course.cno
        RIGHT JOIN stu_spec
        ON stu_info.pno = stu_spec.pno
        """
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()
        print("-" * 100)
        self.sprint(sql)
        for row in cursor.fetchall():
            print(row)
        cursor.close()

    def select_inner_join(self):
        sql = """
        SELECT stu_info.sno, stu_info.name, stu_course.score, stu_spec.spec 
        FROM stu_info 
        INNER JOIN stu_course
        ON stu_info.cno = stu_course.cno
        INNER JOIN stu_spec
        ON stu_info.pno = stu_spec.pno
        """
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()
        print("-" * 100)
        self.sprint(sql)
        for row in cursor.fetchall():
            print(row)
        cursor.close()

    def select_full_join(self):
        if self.dbdriver == sqlite3:
            print("-" * 100)
            print("RIGHT and FULL OUTER JOINs not supportedin Sqlite3")
            return
        if self.dbdriver == pymysql:
            print("-" * 100)
            print("FuLl JOIN not supported in MySql")
            return
        sql = """
        SELECT stu_info.sno, stu_info.name, stu_course.score, stu_spec.spec 
        FROM stu_info 
        FULL JOIN stu_course
        ON stu_info.cno = stu_course.cno
        FULL JOIN stu_spec
        ON stu_info.pno = stu_spec.pno
        """
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()
        print("-" * 100)
        self.sprint(sql)
        for row in cursor.fetchall():
            print(row)
        cursor.close()

    def union_right_left(self):
        if self.dbdriver == sqlite3:
            print("RIGHT and FULL OUTER JOINs not supported in sqlite3")
            return
        sql = """
        SELECT stu_info.sno, stu_info.name, stu_course.course, stu_course.score
        FROM stu_info RIGHT JOIN stu_course on stu_info.cno = stu_course.cno
        UNION
        SELECT stu_info.sno, stu_info.name, stu_course.course, stu_course.score
        FROM stu_info LEFT JOIN stu_course on stu_info.cno = stu_course.cno
        """
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()
        print("-" * 100)
        self.sprint(sql)
        for row in cursor.fetchall():
            print(row)
        cursor.close()

    def select_table(self, table_name):
        sql = """select * from {}""".format(table_name)
        self.cursor.execute(sql)
        self.conn.commit()
        print("[{}]".format(table_name))
        for row in self.cursor.fetchall():
            print(row)

    def sprint(self, sql):
        for s in sql.split2('\n'):
            print(s.strip())

    def select_tables(self):
        print("=" * 100)
        for table in ["stu_info", "stu_course", "stu_spec"]:
            self.select_table(table)
            if table != 'stu_spec':
                print("-" * 100)
        print("=" * 100)


def test_sql(db, create_tables=False):

    t62 = Task62(db)

    if create_tables:
        print("CREATE tables:")
        t62.create_tables()
        print("INSERT tables:")
        t62.insert_tables()

    print("SHOW talbes:")
    if db == sqlite3:
        t62.show_sqlite_tables()
    if db == pymysql:
        t62.show_mysql_tables()

    print("SElECT * FROM tables:")
    t62.select_tables()

    print("LEFT JOIN:")
    t62.select_left_join()

    print("RIGHT JOIN:")
    t62.select_right_join()

    print("UNION JOIN:")
    t62.union_right_left()

    print("INNER JOIN:")
    t62.select_inner_join()

    if db == sqlite3:
        print("FULL JOIN:")       # MySql不支持全连接
        t62.select_full_join()

    print("select fieldlist from talbe1, table2, ... where ...:")
    t62.select_from_multi_tables()

    t62.close()


if __name__ == "__main__":
    test_sql(sqlite3, create_tables=True)
    # test_sql(pymysql, create_tables=True)
